# -*- coding: utf-8 -*-
#
# 查询类相关
#

import datetime
import os
import json
import time
import logging
from collections import OrderedDict as SortedDict
import re
from django.db import connection, connections
from django.db.models import Q
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render_to_response, HttpResponseRedirect

from center.settings import TEMPLATE_DIRS, DEBUG
from public_util.util import trace_msg
# from ...models.log import LogDefine, DictDefine
from ...models.query import Query
from package.views.base import md5, GlobalPathCfg
from .exprot_file import QueryExprot, ExportPath
from ...models.query import QueryAnalysis
from public_util.util.AutoUrl import Route
from package.views.base import notauth

logger = logging.getLogger()


def get_field_id_for_dict(field_dict, value):
    _r = value
    for k, v in field_dict.items():
        if value.strip() == v:
            _r = k
            break
    return str(_r)


@Route('^interface/query/(\S+)$')
@notauth
def interface(request, query_name, template='query_csv'):
    '''查询接口
    '''
    if not hasattr(request, 'admin'):
        pass

    return query_view(request, query_name=query_name, is_query_do=True)


@Route('^query/widget/(\S+)')
@Route('^query/widget[/]?$')
def query_widget(request, query_name):
    return query_view(request, query_name=query_name, is_query_do=False, template='query/query_widget.html')


@Route('^query/view/(\S+)$')
@Route('^query/view$')
def query_view(request, query_name='', query_model=None, is_query_do=False,
               list_data_handle=False, template='query/query_view.html'):
    """
    查询视图,request 传入
    @param query_name:查询名称
    @param is_query_do: 是否执行查询
    """
    logger.info(query_name + ' --------------------------------')
    now = datetime.datetime.now()
    _g = request.REQUEST.get
    _gl = request.REQUEST.getlist
    query_name = query_name or _g('query_name', '') or _g('name', '')
    query_id = int(_g('query_id', '') or _g('qid', '') or 0)
    # 不传id则使用查询名
    if query_id:
        the_query = Query.objects.filter(id=query_id).first()
    elif query_name:
        the_query = Query.objects.using('read').filter(name=query_name).first()
    elif query_model:
        the_query = query_model
    else:
        return HttpResponse('没有 ID[%s] 或者  [%s] 的查询' % (query_id, query_name))
    if not the_query:
        return HttpResponse('没有 ID[%s] 或者  [%s] 的查询' % (query_id, query_name))
    params = dict(request.GET)
    params.update(dict(request.POST))  # 必须为POST

    query_analysis = QueryAnalysis(the_query, params)
    # is_center_query = the_query.query_source
    is_center_query = 1
    is_export = _g('is_export', 'False') == 'true'

    if is_export:  # 导出
        return query_export_do(request, query_analysis)
    if is_query_do or request.method == 'POST' or request.REQUEST.get('query_do') or request.GET.get('template', ''):
        return query_do(request, query_analysis, list_data_handle=list_data_handle)

    field_configs = SortedDict()
    conditions_configs = []

    # for cloumn in the_query.selects:
    #     field_config_item = the_query.field_config.get(cloumn, {})
    #     field_configs[cloumn] = field_config_item
    # for k in the_query.field_config.keys():
    #     field_config_item = the_query.field_config[k]
    #     if field_config_item.get('single', ''):
    #         default_value = DictDefine.get_dict_for_key(field_config_item.get('dict', ''))
    #     else:
    #         default_value = params.get(field_config_item.get('name', ''), [''])[0]
    #
    #     field_config_item['value'] = default_value
    #     order_num = int(field_config_item.get('order_num', 0) or 99)
    #     conditions_configs.append((k, field_config_item, order_num))

    conditions_configs.sort(key=lambda x: x[2])

    sd = request.REQUEST.get('sd', '') or now.strftime('%Y-%m-%d 00:00:00')
    ed = request.REQUEST.get('ed', '') or now.strftime('%Y-%m-%d 23:59:59')

    has_sd = query_analysis.has_mark('sd')
    has_ed = query_analysis.has_mark('ed')

    sdate = request.REQUEST.get('sdate', '') or now.strftime('%Y-%m-%d 00:00:00')
    edate = request.REQUEST.get('edate', '') or now.strftime('%Y-%m-%d 23:59:59')

    has_sdate = query_analysis.has_mark('sdate')
    has_edate = query_analysis.has_mark('edate')
    has_conditions = query_analysis.has_conditions()

    plugin_templates = []
    if the_query.template_name:
        for template_name in the_query.template_name.split(','):
            # if template_name.strip():
            #     if template_name == "邮件列表":
            #         dj = json.dumps(eval(DictValue.objects.get(dict_id=88).json_dict))
            plugin_templates.append('query/plugins/%s.html' % template_name.strip())
    return render_to_response(template, locals())


def query_export_do(request, query_analysis):
    """查询导出文件"""
    _g = request.REQUEST.get
    _gl = request.REQUEST.getlist
    export_key = _g('export_key', '')
    is_finish = _g('is_finish', '')  # 完成标记 合并文件
    export_type = int(_g('export_type', '') or 0)
    q_server_id = int(request.REQUEST.get('server_id', '') or 0)
    page_num = int(_g('page_num', '') or 0) or 1  # 页码
    file_type = request.REQUEST.get('file_type', '')
    page_size = 10000
    request.REQUEST.setlist('page_size', [page_size])

    fields = query_analysis.query.selects
    is_summary = _g('is_summary', '')  # 汇总

    query_exprot = QueryExprot(export_key, export_type)
    if is_finish:  # 完成,文件合并
        merge_file_name = query_exprot.merge_files(fields)
        _r = {"url": '/export/query/%s' % merge_file_name, "export_key": query_exprot.export_key,
              'export_type': query_exprot.export_type}
    elif is_summary:  # 文件汇总
        merges = [int(x) for x in request.REQUEST.getlist('merges')]
        done_summary_file_name = query_exprot.summary_file(merges)
        new_url = '/static/export/query/%s' % done_summary_file_name
        return HttpResponseRedirect(new_url)
    else:
        export_file_name = '%s_%s' % (query_analysis.query.name, q_server_id)
        total_record, list_data = query_do(request, query_analysis, built_in=True)
        _r = query_exprot.gene_file(export_file_name, list_data, fields, page_num, page_size, total_record)
    _r["code"] = 0
    _r["server_id"] = q_server_id
    return HttpResponse(json.dumps(_r))


def query_do(request, query_analysis, built_in=False, list_data_handle=None):
    """执行查询"""
    template_name = request.REQUEST.get('template', '') or 'json'
    now = getattr(request, '_start_time', time.time())
    _g = request.REQUEST.get
    _gl = request.REQUEST.getlist
    err_msg = ''

    params = (request.GET)

    total_record = total_page = 0
    page_num = int(_g('page_num', '') or 0) or 1  # 页码
    page_size = int(_g('page_size', '') or 0) or 50  # 页数
    session_id = request.REQUEST.get('session_id', '')

    is_ajax = request.is_ajax() or _g('ajax', False)
    list_data = []
    tfoot_data = []
    query_sql = count_sql = ''
    try:
        # if query_analysis.query.query_source:
        # conn = connections[query_analysis.query.get_query_source_display()]
        conn = connection

        if not err_msg:
            fields = query_analysis.query.selects
            query_analysis.set_limit(page_size, page_num)
            query_analysis.query_sql_handle()

            # 设置排序
            sort_type = _g('sort_type', '')
            sort_fields_index = int(_g('sort_index', '') or -1)
            if sort_fields_index >= 0 and len(fields) >= sort_fields_index:
                sort_field_name = fields[sort_fields_index]
                sort_key = query_analysis.query.field_config.get(sort_field_name, {}).get('name', '')
                if sort_key and sort_type:
                    query_analysis.set_order(sort_key, sort_type)

            count_sql = query_analysis.get_count_sql()
            query_sql = query_analysis.get_query_sql()

            fields = query_analysis.query.selects  # 查询的表头

            logger.info(query_sql)
            print(query_sql)

            cursor = conn.cursor()
            total_record = get_query_result_cout(conn, count_sql)

            if total_record:
                # query_sql_key = md5('%s_%s' % (query_sql, server_id))
                total_page = total_record // page_size
                if total_record % page_size != 0:
                    total_page += 1
                list_data = get_query_result(conn, query_sql)
                list_data = query_display_process(query_analysis, list_data, page_num, page_size)
                tfoot_sql = query_analysis.get_tfoot_sql()

                # if tfoot_sql:
                #     tfoot_sql_key = md5('%s_%s' % (tfoot_sql, server_id))
                #     tfoot_data, result_cache_time = cache_func('tfoot_%s' % tfoot_sql_key, get_query_result,
                #                                                (conn, tfoot_sql), timeout=cache_time,
                #                                                cache_not_set=built_in)
    except Exception as e:
        err_msg = trace_msg()
        print(err_msg)
        err_msg = '%s\nthe_sql:%s' % (err_msg, query_sql)

    if list_data_handle and callable(list_data_handle):
        list_data = list_data_handle(list_data)
    if built_in:  # 导出
        return (total_record, list_data)

    exec_time = '%.3f' % (time.time() - now)
    response = render_to_response('query/return/%s.html' % template_name, locals())
    response['Order-Record-Count'] = total_record
    response['Page-Num'] = page_num
    response['Page-Size'] = page_size
    response['Total-Page'] = total_page
    return response


# @Route('^query/clear/cache')
# def query_clear_cache(request):
#     '''清除查询缓存
#     '''
#     clear_cache(CACHE_TYPE.LOG_CACHE)
#     return HttpResponse('成功!')
#     # return render_to_response('feedback.html')


def get_query_result_cout(conn, sql):
    return int(get_query_result(conn, sql)[0][0])


def get_query_result(conn, sql):
    cursor = conn.cursor()
    setattr(cursor, 'use_cache', False)  # 设置一个属性给缓存中间件使用
    cursor.execute(sql)
    _r = cursor.fetchall()
    return _r


def query_display_process(query_analysis, list_data, page_num, page_size):
    """值定义处理"""
    fields = query_analysis.query.selects  # 查询的表头
    field_configs = []
    for field_name in fields:
        field_config = query_analysis.query.field_config.get(field_name, {})
        field_config['field_name'] = field_name
        dict_key = field_config.get('dict', '')
        value_defs = {}
        json_values = {}
        # if dict_key:
        #     if '[' in dict_key:
        #         dict_key_list = json.loads(dict_key)
        #         for key in dict_key_list:
        #             if isinstance(key,dict):
        #                 _r = key
        #             else:
        #                 _r =DictDefine.get_dict_for_key(key, reverse=False)
        #             json_values.update(_r)
        #     else:
        #         value_defs = DictDefine.get_dict_for_key(dict_key, reverse=False)
        # else:
        #     value_defs = {}
        field_config['value_defs'] = value_defs
        field_config['json_values'] = json_values
        field_configs.append(field_config)
        if field_name == '排名':
            field_config['rank'] = True
    new_list_data = []
    list_data = list(list_data)
    n = (page_num - 1) * page_size if page_num > 1 else 0
    for row in list_data:
        item = list(row)
        item_len = len(item)
        n += 1
        for i, _ in enumerate(fields):
            if item_len > i:
                field_config = field_configs[i]
                if field_config.get('rank', False):
                    item[i] = n
                else:
                    item[i] = display_format(field_config, item[i])
        new_list_data.append(item)

    return new_list_data


def display_format(field_config, value):
    """转格式"""
    field_name = field_config.get('field_name', '')
    def_values = field_config['value_defs']
    json_values = field_config['json_values']

    if isinstance(value, (datetime.datetime, datetime.date)):
        if '时间' in field_name:
            value = value.strftime('%Y-%m-%d %H:%M:%S')
        elif '日期' in field_name:
            value = value.strftime('%Y-%m-%d')
        elif '小时' in field_name:
            value = value.strftime('%Y-%m-%d %H')
        else:
            value = value.strftime('%Y-%m-%d %H:%M:%S')
    elif value is not None and def_values:
        tmp_value = def_values.get(value,
                                   def_values.get(str(value), value)
                                   )
        if field_config.get('merge_value', False):
            value = '%s%s' % (tmp_value, '(%s)' % value if value else '')
        else:
            value = tmp_value
    elif value is not None and json_values:  # 解析json数据
        rs = re.compile('[A-Za-z0-9_-]+')
        item_list = set(rs.findall(value))
        is_merge = field_config.get('merge_value', False)
        for item in item_list:
            if item in json_values:
                value = value.replace(item, '%s(%s)' % (json_values[item], item) if is_merge else json_values[item])
    return value


@Route('^query/list')
def query_list(request, query_id=''):
    """查询列表"""
    query_id = int(request.REQUEST.get('query_id', 0))
    query_type_id = int(request.REQUEST.get('query_type', -1))
    all_query_list = Query.objects.using('write').all().order_by("id")

    if query_id:
        list_model = all_query_list.filter(id=query_id)
    else:
        list_model = all_query_list

    allow_menu_keys = request.allow_menu.keys()

    return render_to_response('query/query_list.html', locals())


@Route('^query/edit')
def query_edit(request, query_id=0, log_type=0):
    """查询类编辑"""
    if request.REQUEST.get('edit_type', ''):
        return QueryTemplateEdit(request)()
    is_copy = request.REQUEST.get('is_copy', '')
    query_id = int(request.REQUEST.get('query_id', '') or 0)
    if query_id:
        model = Query.objects.using('read').get(id=query_id)
        if model.cache_validate is None:
            model.cache_validate = 0
        if is_copy:
            model.id = 0
            model.name = '%s-copy' % model.name
    else:
        model = Query()
        model.id = query_id
        model.name = ''
        model.cache_validate = 0

    list_menu = request.admin.get_resource('menu').using('write')
    current_menu = list_menu.filter(url='/query/view/%s' % model.name).first()

    return render_to_response('query/query_edit.html', locals())


@Route('^query/save')
def query_save(request, query_id=0):
    _g = request.REQUEST.get
    query_id = int(_g('query_id', '') or 0)
    parent_menu_id = int(_g('parent_id', -1))
    if query_id:
        model = Query.objects.get(id=query_id)
        model.old_name = model.name  # 记录旧名用来修改菜单的名字
    else:
        model = Query()
    model.parent_menu_id = parent_menu_id  # 将id赋值给模型类
    err_msg = ''
    model.log_type = 0
    model.select = request.REQUEST.get('select', '')
    model.remark = request.REQUEST.get('remark', '')
    model.name = request.REQUEST.get('name', '')
    model.where = request.REQUEST.get('where', '')
    model.group = request.REQUEST.get('group', '')
    model.order = request.REQUEST.get('order', '')
    model.cache_validate = int(request.REQUEST.get('cache_valid', 0))
    model.order_type = int(request.REQUEST.get('order_type', '0'))
    model.sql = request.REQUEST.get('sql', '')
    model.other_sql = request.REQUEST.get('other_sql', '')
    model.field_config = request.REQUEST.get("field_config", "")
    model.template_name = request.REQUEST.get('template_name', '')
    model.middle_query = int(request.REQUEST.get('middle_query', 0))
    # model.cron_data_source = int(request.REQUEST.get('cron_data_source', 0))
    # model.query_source = int(request.REQUEST.get('query_source', 0))
    if request.REQUEST.get('open_cron', 0):
        # 开启了中间表获取数据的定时任务时处理
        model.open_cron = 1
        model.cron_key = request.REQUEST.get('cron_key', None)
        model.cron_create_sql = request.REQUEST.get('cron_create_sql', '')
        model.cron_select_sql = request.REQUEST.get('cron_select_sql', '')
        model.cron_insert_sql = request.REQUEST.get('cron_insert_sql', '')
        if not model.cron_create_sql or not model.cron_select_sql or not model.cron_insert_sql:
            err_msg += "开启中间表定时任务后，需要填写好3个sql语句；"
        if not model.cron_key:
            err_msg += "定时任务key未填写；"
        else:
            if Query.objects.filter(~Q(id=query_id), cron_key=model.cron_key).first():
                err_msg += "定时任务key已存在；"
    else:
        # 判断是否是开启后再关闭
        if model.open_cron:
            model.open_cron = 2
    if not err_msg:
        try:
            save_id = int(_g('save_id', '') or 0)
            if query_id:  # 更新
                exist_id = False
                exist_name = False
                if query_id != save_id:
                    exist_id = Query.objects.filter(id=save_id).exists()
                if getattr(model, 'old_name', '') != model.name:
                    exist_name = Query.objects.filter(name=model.name).exists()
                if not exist_id and not exist_name:
                    model.id = save_id
                    model.save(using='write')
                else:
                    if exist_id:
                        err_msg += 'ID 已经存在'
                    if exist_name:
                        err_msg += '查询名称 已经存在'
            else:
                if Query.objects.filter(name=model.name).exists():
                    err_msg = '查询名称 已经存在'
                else:
                    model.save(using='write')
        except Exception as e:
            err_msg = trace_msg()
            print('query save error:', e)
    return render_to_response('feedback.html', locals())


@Route('^query/remove')
def query_remove(request, query_id=0):
    _g = lambda x, y='': request.POST.get(x, request.GET.get(x, y))
    model_id = int(query_id)

    if model_id == 0:
        model_id = int(_g('query_id', '0'))

    if model_id > 0:
        model = Query.objects.get(id=model_id)

        model.delete(using='write')
    return render_to_response('feedback.html')


class QueryTemplateEdit(object):
    __static_path = GlobalPathCfg().get_static_folder_path()
    __template_dir = TEMPLATE_DIRS[0]

    def __init__(self, request):
        self.r = request
        self.edit_type = self.r.REQUEST.get('edit_type', 'query')
        self.template_name = request.REQUEST.get('template_name')
        self.file_path = self.get_path()
        self.action = self.r.REQUEST.get('action')

    def get_path(self):
        return os.path.join(self.__template_dir, 'query', 'plugins', '%s.html' % self.template_name)

    def save(self, data):
        with open(self.file_path, 'wb') as f:
            f.write(data)

    def __call__(self):
        template_name = self.template_name
        edit_type = self.edit_type
        if self.action == 'save':
            file_content = self.r.REQUEST.get('code', '')
            self.save(file_content)
        elif os.path.exists(self.file_path):
            with open(self.file_path, 'rb') as f:
                file_content = f.read()
        else:
            file_content = ''
        return render_to_response('query/query_template_edit.html', locals())
